<?php
/* ******************************
 *	Request aJax actions
 * ******************************
*/
require_once('../includes/classes/core.php');
$action = $_REQUEST['act'];
$start		=	$_REQUEST['start'];
$end		=	$_REQUEST['end'];



function checkgroup($user){
	$res = mysql_fetch_assoc(mysql_query("
			SELECT crystal_users.group_id
			FROM    crystal_users
			WHERE  crystal_users.id = $user
			"));
	return $res['group_id'];
	 
}


switch ($action) {
    case 'chart':
	$user_id	= $_SESSION['USERID'];
	$group=checkgroup($user_id);
	$filter = '';
	$filter1 = '';
		if ($group != 2 && $group != 3) {
		$res_row = mysql_fetch_assoc(mysql_query("SELECT 	crystal_users.id as person_id
													FROM 	`crystal_users`
													WHERE 	`crystal_users`.`id` = $user_id"));
		
		$filter = 'AND asterisk_incomming.user_id ='. $res_row[person_id];
		$filter1 = 'AND asterisk_outgoing.user_id ='. $res_row[person_id];
		}
        $in = mysql_query("SELECT  week_day.`name`,				
                                				COUNT(asterisk_incomming.id)
												FROM  week_day
												LEFT JOIN asterisk_incomming ON WEEKDAY(DATE_ADD(asterisk_incomming.datetime,INTERVAL 1 DAY ))+1=week_day.id
																AND DATE(asterisk_incomming.datetime) BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -6 DAY)) AND DATE(NOW()) 
																AND disconnect_cause!='ABANDON' $filter
	
												GROUP BY  week_day.`name`
												ORDER BY DATE(asterisk_incomming.datetime)");
        while ( $aRow = mysql_fetch_array( $in ) )
        {
        
            $data[inc][series][0][data][] = (float) $aRow[1];
        
        }
        
        $out = mysql_query( "SELECT  week_day.`name`,    
                            		 COUNT(asterisk_outgoing.id)
	                        FROM    week_day
	                        LEFT JOIN asterisk_outgoing ON WEEKDAY(DATE_ADD(asterisk_outgoing.call_datetime,INTERVAL 1 DAY ))+1=week_day.id
									AND DATE(asterisk_outgoing.call_datetime) BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -6 DAY)) AND DATE(NOW())
									AND asterisk_outgoing.extension IN (501,502,503,504)
									AND asterisk_outgoing.file_name!='' $filter1
	                        GROUP BY week_day.`name`
	                        ORDER BY DATE(asterisk_outgoing.call_datetime)");
        while ( $aRow = mysql_fetch_array( $out ) )
        {
        
            $data[out][series][0][data][] = (float) $aRow[1];
        
        }
        
        $local = mysql_query("SELECT week_day.`name`,    
                                    COUNT(*)
                                FROM asterisk_outgoing
                                JOIN week_day ON WEEKDAY(DATE_ADD(asterisk_outgoing.call_datetime,INTERVAL 1 DAY ))+1=week_day.id
                                WHERE DATE(asterisk_outgoing.call_datetime) BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -6 DAY)) AND DATE(NOW())
                                AND asterisk_outgoing.extension NOT IN(501,502,503,504) 
                                AND LENGTH(asterisk_outgoing.extension)=3
                                GROUP BY WEEKDAY(asterisk_outgoing.call_datetime)
                                ORDER BY DATE(asterisk_outgoing.call_datetime)");

        while ( $aRow2 = mysql_fetch_array( $local) )
        {
        
            $data[xAxis][] = $aRow2[0];
            $data[local][series][0][data][] = (float) $aRow2[1];
        
        }
        $dedline=mysql_query("SELECT 	crystal_users.`name` AS `user`,
                        				week_day.`name`,
                        				COUNT(task1.id)
                        FROM  week_day
                        LEFT  JOIN task ON WEEKDAY(DATE_ADD(task.date,INTERVAL 1 DAY ))+1=week_day.id AND DATE(task.fact_end_date) BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -6 DAY)) AND DATE(NOW())
                        RIGHT JOIN crystal_users ON crystal_users.id
                        left  JOIN task AS task1 ON crystal_users.id=task1.responsible_user_id AND task1.id=task.id
        				WHERE   crystal_users.id!=202 AND crystal_users.id!=451 AND crystal_users.id!=589
                        GROUP by  crystal_users.id, week_day.id         ");
        $i=-1;
        $j=0;
        $k=0;
        while ( $aRow3 = mysql_fetch_array( $dedline) )
        {
            
            if ($n!=$aRow3[0]) {
                $data[name][]=$aRow3[0];
                $i++;
            }
            
            $data[dedline][xAxis][]=$aRow3[1];
            $data[dedline][data][]= array((float)$i, (float)$k, $aRow3[2]);
            $j++;
            $n=$aRow3[0];
            $k=$j%7;
        
        }
        
        
        $wait_time=mysql_query("SELECT   crystal_users.`name` AS operator,
                                (MAX(asterisk_incomming.wait_time)) AS max_wait_time,                				 
                                 (ROUND(SUM(asterisk_incomming.wait_time)/COUNT(*),2)) AS sashualo,                                 				 
                                (MIN(asterisk_incomming.wait_time)) AS min_wait_time   
                                				 
                                FROM  asterisk_incomming
                                JOIN    crystal_users ON crystal_users.id=asterisk_incomming.user_id
                                WHERE   disconnect_cause!='ABANDON' AND DATE(asterisk_incomming.call_datetime)=DATE(NOW()) $filter
                                GROUP BY crystal_users.`name`
                                ORDER BY sashualo DESC");
        $j=0;
        
        while ( $aRow4 = mysql_fetch_array( $wait_time) )
        {   
            $data[wait_time][xAxis][]=$aRow4[0];
            for ($i = 0; $i < 3; $i++) {                                        
                $data[wait_time][series][$i][name]= 'lll';
                $data[wait_time][series][$i][type]= "column";
                $data[wait_time][series][$i][data][]= (float)$aRow4[$i+1];            
        }       }


        $data[mytask]=mytask();
        $data[news]=news();
        $data[service_level]=service_level();
        
        
        echo json_encode( $data );
        break;

    default:
        echo "null";
}
function mytask(){
$user_id	= $_SESSION['USERID'];
$group=checkgroup($user_id);
$filter = '';

if ($group != 2 && $group != 3) {
$res_row = mysql_fetch_assoc(mysql_query("SELECT 	crystal_users.id as person_id
											FROM 	`crystal_users`
											WHERE 	`crystal_users`.`id` = $user_id"));

$filter = "AND (task.responsible_user_id ='$res_row[person_id]' or task.responsible_user_id1 =$res_row[person_id])";

}

 
$task_now_count1 = mysql_fetch_row(mysql_query("   SELECT IF(COUNT(*)>0,CONCAT('<span style=\"color:red;\">',COUNT(*),'</span>'), COUNT(*)),
COUNT(*)
FROM `task`
WHERE task.`status` = 0 and task.actived=1 $filter"));
$task_process_count1 = mysql_fetch_row(mysql_query("   SELECT COUNT(*)
FROM `task`
WHERE task.`status` = 1 and task.actived=1 $filter"));
$task_done_count1 = mysql_fetch_row(mysql_query("   SELECT COUNT(*)
FROM `task`
WHERE task.actived=1  AND (task.`status` = 2 or task.`status` = 3) $filter"));

$row = mysql_fetch_row(mysql_query("SELECT crystal_users.group_id
    FROM `crystal_users`
    WHERE crystal_users.id = '$user_id'"));
    if($task_now_count1[1] > 0){
    $new_task1 = '<span style="background-color: red; border-radius: 2px 2px 2px 2px; color: #FFFFFF; padding: 1px 2px; position: absolute; top: -5px; left: -6px;">NEW</span>';
}
$my_task_hide1 = '';
if($row[0] != ''){
$my_task_hide1 = '
<div id="ctl00_ContentPlaceHolder1_tile_Declarations_NEW" class="tile_small" style="background:#9CC6F0; margin-top: 0px">
<p style="display: block!important;margin:8px 0 10px;">ჩემი დავალება</p>

'.$new_task1.'

<div onclick="location.href=\'index.php?pg=13#tab-1\'" class="tile_waybill_notification tt-wrapper" title="გადაცემულია გასარკვევად" style="float: left; cursor: pointer;">
<span id="ctl00_ContentPlaceHolder1_lblSavedDecl">'.$task_now_count1[0].'</span>
</div>
    <div onclick="location.href=\'index.php?pg=13#tab-2\'" class="tile_waybill_notification tt-wrapper" title="გარკვევის პროცესშია" style="float: left; cursor: pointer;">
        <span id="ctl00_ContentPlaceHolder1_lblSavedDecl">'.$task_process_count1[0].'</span>
            </div>
                <div onclick="location.href=\'index.php?pg=13#tab-3\'" class="tile_waybill_notification tt-wrapper" title="დასრულებულია და მოგვარებულია, აცნობეთ" style="float: left; margin-top: 5px; cursor: pointer;">
                    <span id="ctl00_ContentPlaceHolder1_lblSavedDecl">'.$task_done_count1[0].'</span>
             </div>
   
     </div>
                                     ';
                 }else {
                     $my_task_hide1 = "";
                }
                 return $my_task_hide1;
}
function news(){
    $task_process_count1  = mysql_fetch_row(mysql_query("SELECT COUNT(*)
                                                    FROM action
                                                    WHERE DATE(end_date)<=DATE(NOW())"));
    $task_now_count1= mysql_fetch_row(mysql_query("SELECT COUNT(*)
                                                    FROM action
                                                    WHERE DATE(end_date)>DATE(NOW())"));

$my_task_hide1 = '
<div id="ctl00_ContentPlaceHolder1_tile_Declarations_NEW" class="tile_small" style="background:#9CC6F0; margin-top: 0px">
    <p style="display: block!important;margin:8px 0 10px;">სიახლეები</p>   
    <div onclick="location.href=\'index.php?pg=47#tab-0\'" class="tile_waybill_notification tt-wrapper" title="მიმდინარე " style="float: left; cursor: pointer;">
        <span id="ctl00_ContentPlaceHolder1_lblSavedDecl" style="color:red;">'.$task_now_count1[0].'</span>
    </div>
    <div onclick="location.href=\'index.php?pg=47#tab-1\'" class="tile_waybill_notification tt-wrapper" title="არქივი" style="float: left; cursor: pointer;">
        <span id="ctl00_ContentPlaceHolder1_lblSavedDecl">'.$task_process_count1[0].'</span>
    </div>
</div>
        ';

                 return $my_task_hide1;
}
function service_level() {



    $res_service_level = mysql_query("
            SELECT  `wait_time`                                				
            FROM 	`asterisk_incomming`
            WHERE	`wait_time` IS NOT NULL 
            AND DATE(`asterisk_incomming`.`call_datetime`) = DATE(NOW()) 
        ");
    $w15 = 0;
    $w30 = 0;
    $w45 = 0;
    $w60 = 0;
    $w75 = 0;
    $w90 = 0;
    $w91 = 0;
    	
    	
    	
    	
    while ($res_service_level_r = mysql_fetch_assoc($res_service_level)) {
    	
        if ($res_service_level_r['wait_time'] < 15) {
        $w15++;
			}
		
    			if ($res_service_level_r['wait_time'] < 30){
    			$w30++;
			}
		
    			if ($res_service_level_r['wait_time'] < 45){
    			$w45++;
        }
        	
        if ($res_service_level_r['wait_time'] < 60){
        $w60++;
        }
        	
        if ($res_service_level_r['wait_time'] < 75){
        $w75++;
        }
		
            if ($res_service_level_r['wait_time'] < 90){
            $w90++;
        }
        	
        $w91++;
            	
    }
    
    $d30 = $w30 - $w15;
    $d45 = $w45 - $w30;
    $d60 = $w60 - $w45;
    $d75 = $w75 - $w60;
    $d90 = $w90 - $w75;
    $d91 = $w91 - $w90;
    	
    $mas = array($w15,$d30,$d45,$d60,$d75,$d90,$d91);
    $call_second=array('15 წამში','30 წამში','45წამში','60 წამში','75 წამში','90 წამში','90+წამში');
   return  array('name' => $name, 'unit' => $unit, 'mas' => array(array('data' => $mas)), 'call_second' => $call_second);
    ;
}
?>